package com.o2opuzi.lib.db;

import java.beans.PropertyDescriptor;
import java.io.Serializable;
import java.lang.reflect.ParameterizedType;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.concurrent.ConcurrentHashMap;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.util.StringUtils;


/**
 * 
 * @author yuehanjiang
 *
 * @param <En>
 * @param <Id>
 */
@SuppressWarnings("rawtypes")
public  class Model<En extends Model,Id extends Object>  implements Serializable{
	
	private static final long serialVersionUID = -533153753650880217L;
	
	private transient static DbHelper db = new DbHelper();
	
	protected transient final Log  log = LogFactory.getLog(getClass());
	
	private transient  static final Map<String,TableMapping> TableMap = new ConcurrentHashMap<String, TableMapping>();
		
	private transient List<String> fields;
	
	private transient StringBuilder tables;
	 
	private transient StringBuilder sqlBuilder;
	
	private transient Map<String, Object> setFieldMap;
		
	protected transient List<Object> params;
	
	protected DbHelper db() {
		return db;
	}
	

	public void init(){
		this.tables =new StringBuilder();
		this.fields = new ArrayList<String>();
		this.params = new ArrayList<Object>();
		this.setFieldMap = new LinkedHashMap<String, Object>();
		this.sqlBuilder = new StringBuilder();
	}
	
	/**
	 */
	public Model() {
		this.init();
		Class<En> enClass = getEnClass();
		if(enClass != null){
			tables(enClass);
		}
	}
	
	@SuppressWarnings("unchecked")
	private En self(){
		return (En) this;
	}
	
	public Model(String table) {
		this();
		from(table);
	}
	
	public Model(List tables){
		this();
		for (int i = 0; i < tables.size(); i++) {
			if(i > 0){
				this.tables.append(" , ");
			}
			this.tables.append(tables.get(i).toString())
					        .append(" ");
		}
	}
	
	public Model(Class<?> table) {
		this();
		cols();
		tables(table);
	}
	
	
	public En addTermArray(Terms terms){
		List<Collection<Object>> array = terms.getTermArray();
		for (int i = 0; i < array.size(); i++) {
			if(i > 0){
				and();
			}
			Object[] objects = array.get(i).toArray();
			if(objects.length == 2){
				eq(objects[0].toString(), objects[1]);
			}else if(objects.length == 3){
				term(objects[0].toString(),objects[1].toString(), objects[2]);
			}
		}
		return self();
	}
	
	
	public Model(String columns,String table) {
		cols(columns);
		from(table);
	}
	
	public Model(String[] columns,String table) {
		cols(columns);
		from(table);
	}
	
	public Model(String columns,Class<?> tableClass) {
		cols(columns);
		tables(tableClass);
	}
	
	public Model(String[] selects,Class<?>[] tableClass) {
		cols(selects);
		tables(tableClass);
	}
	
	/**
	 * @param value
	 * @return
	 */
	public En addParam(Object value){
		this.params.add(value);
		return self();
	}
	
	/**
	 * @param params
	 * @return
	 */
	public En addParams(Object[] params){
	    for (int i = 0; i < params.length; i++){
	    	this.params.add(params[i]);
	    }
		return self();
	}
	
	/**
	 * @param name
	 * @param value
	 * @return 
	 */
	public En set(String name,Object value){
		this.setFieldMap.put(name, "?");
		this.params.add(value);
		return self();
	}
	
	public En setField(String name,String value,Object...params){
		this.setFieldMap.put(name, value);
		for (int i = 0; i < params.length; i++) {
			this.params.add(params[i]);
		}
		return self();
	}
	
	public En cols(){
		return cols("*");
	}
	
	public En cols(String...columns){
		/*
		this.columns.append(" ");
		for (int i = 0; i < columns.length; i++){
			if(i > 0)
				this.columns.append(",");
			this.columns.append(columns[i]);
		}
		*/
		this.fields.addAll(Arrays.asList(columns));
		return self();
	}

	
	public En cols(List<String> columns){
		/*
		String[] cols = new String[columns.size()];
		cols(columns.toArray(columns.toArray(cols)));
		*/
		this.fields.addAll(columns);
		return self();
	}
	
	/**
	 * @param columns
	 * @return
	 */
	public En excludeCols(String...columns){
		TableMapping TableMapping = getTableMapping();
		List<String> list = new ArrayList<String>();
		list.add(TableMapping.getId());
		for (PropertyDescriptor pd : TableMapping.getColumns()) {
			boolean isEx = false;
			for(int i =0; i < columns.length;i++){
				if(pd.getName().equals(columns[i])){
					isEx = true;
					break;
				}
			}
			if(!isEx){
				list.add(pd.getName());
			}
		}
		return cols(list.toArray(new String[list.size()]));
	}
	
	public En excludeCols(List<String> columns){
		for (String string : columns) {
			excludeCols(string);
		}
		return self();
	}
	
	
	private En from(String from){
		 tables.append(" ")
		                .append(from);
		return self();
	}
	
	
	private En tables(Class<?>...enClass){
		String from = "";
		for (int i = 0; i < enClass.length; i++) {
			TableMapping info = getTableMapping(enClass[i]);
			if(i > 0)
				from+=",";
			from+=info.getName();
		}
		return from(from);
	}
	
	public En where(){ 
		where("");
		return self();
	}
	
	/**
	 * @param where
	 * @param params
	 * @return
	 */
	public En where(String where,Object...params) {
		/*
	    if(!iswhere){
	    	conditions.append(" ");
	    	conditions.append("where");
	    	this.iswhere = true;
	    }
	    */
	    sqlBuilder.append(" WHERE ");
	    sqlBuilder.append(where);
	    this.addParams(params);
		return self();
	}
	
	public En and(){
		return and("");
	}
	
	public En and(String str){
		sqlBuilder.append(" and  ")
		    		    .append(str);
		return self();
	}
	
	public En and(String str,Object value){
		and().sql(str,value);
		return self();
	}
	
	public En and(String str,Object...params){
		 and().sql(str, params);
		 return self();
	}
	
	/**
	 * 鍒悕
	 * @param as
	 * @return
	 */
	public En as(String as){
		sqlBuilder.append(" as ")
				       .append(as);
		return self();
	}
	
	public En term(String filed,String symbol,Object value){
		return term(filed, symbol, "?", value); 
	}
	
	/**
	 * add conditions
	 * @auth yue571041524
	 * @param filed
	 * @param symbol
	 * @param sql
	 * @param params
	 * @return
	 */
	public En term(String filed,String symbol,String sql,Object...params){
		this.sqlBuilder.append(filed)
						      .append(" ")
						      .append(symbol)
						      .append(" ")
						      .append(sql);
		this.params.addAll(Arrays.asList(params));
		/*
		this.conditions.append(" ")
			   .append(filed)
			   .append(" ")
			   .append(symbol)
			   .append(" ")
			   .append(sql);
		addParams(params);
		*/
		return self();
	}
	
	/**
	 * eq field 
	 * @param filed
	 * @param value
	 * @return
	 */
	public En eq(String filed,Object value){
		return term(filed, "=", value);
	}
	
	public En or(){
		sqlBuilder.append(" or ");
		 return self();
	}
	
	public En eq(String str){
		this.sqlBuilder.append(" ")
								 .append(str);
		return self();
	}
	
	public En like(String filed,Object value){
		return term(filed, "like", value);
	}
	
	public En eq(String filed,String sql,Object...params){
		return term(filed, "=",sql,params);
	}
	
	public En ne(String filed,Object value){
		return term(filed, "<>", value);
	}
	
	public En ne(String filed,String sql,Object...params){
		return term(filed, "<>",sql,params);
	}
	
	public En gt(String filed,Object value){
		return term(filed, ">", value);
	}
	
	public En gt(String filed,String sql,Object...params){
		return term(filed, ">",sql,params);
	}
	
	public En ge(String filed,Object value){
		return term(filed, ">=", value);
	}
	
	public En ge(String filed,String sql,Object...params){
		return term(filed, ">=",sql,params);
	}
	
	public En lt(String filed,Object value){
		return term(filed, "<", value);
	}
	
	public En lt(String filed,String sql,Object...params){
		return term(filed, "<",sql,params);
	}
	
	public En le(String filed,Object value){
		return term(filed, "<=", value);
	}
	
	public En le(String filed,String sql,Object...params){
		return term(filed, "<=",sql,params);
	}
	
	public En ob(String filed,String type){
		sqlBuilder.append(" ")
			 .append("order")
			 .append(" ")
			 .append("by")
			 .append(" ")
			 .append(filed)
			 .append(" ")
			 .append(type);
		return self();
	}
	
	public En order(String order){
		return sql("order by "+order);
	}
	
	public En order(String filed,String type){
		return ob(filed, type);
	}
	
	public En orderBy(String field){
		return ob(field, "asc");
	}
	
	
	public En orderByDesc(String filed){
		return ob(filed, "desc");
	}
	
	public En groupBy(String filed,String type){
		sqlBuilder.append(" ")
			 .append("group")
			 .append(" ")
			 .append("by")
			 .append(" ")
			 .append(filed)
			 .append(" ")
			 .append(type);
		return self();
	}
	
	public En group(String filed){
		sqlBuilder.append(" ")
			 .append("group")
			 .append(" ")
			 .append("by")
			 .append(" ") 
			 .append(filed);
		return self();
	}
	
	public En join(String type,String table,String as){
		sqlBuilder.append(" ")
			 .append(type)
			 .append(" ")
			 .append("join")
			 .append(" ")
			 .append(table);
		if(!StringUtils.isEmpty(as)){
			sqlBuilder.append(" ")
							 .append("as") 
							 .append(" ")
							 .append(as);
		}
		return self();
	}
	
	
	public En in(String field,Object...params){
		sqlBuilder.append(" ")
						 .append(field)
						 .append(" ")
						 .append("in")
						 .append(" ")
						 .append("(");
		for (int i = 0; i < params.length; i++) {
			if(i > 0)
				sqlBuilder.append(",");
			sqlBuilder.append("?");
			this.params.add(params[i]);
		}
		sqlBuilder.append(")");
		return self();
	}
	
	public En in(String field,List<Object> idarras){
		return in(field, idarras.toArray());
	}
	
	public En join(String type,String table){
		return join(type, table,null);
	}
	
	public En join(String type,Class<?> tableClass,String as){
		return join(type,getTableMapping(tableClass).getName(), as); 
	}
	
	public En join(String type,Class<?> tableClass){
		return join(type, getTableMapping(tableClass).getName(),null);
	}
	
	public En leftJoin(String table){
		return leftJoin(table, null);
	}
	
	public En leftJoin(String table,String as){
		return join("left", table,as); 
	}
	
	public En leJoin(Class<?> tableClass){
		return leftJoin(tableClass,null);
	}
	
	public En leftJoin(Class<?> tableClass,String as){
		return join("left", tableClass, as);
	}
	
	
	public En innerJoin(Class<?> tableClass){
		return join("inner", tableClass);
	}
	
	public En innerJoin(String table){
		return join("inner", table);
	}
	
	public En innerJoin(Class<?> tableClass,String as){
		return join("inner", tableClass, as);
	}
	
	public En innerJoin(String table,String as){
		return join("inner",table,as);
	}
	
	public En rightJoin(Class<?> tableClass,String as){
		return join("right", tableClass, as);
	}
	
	public En rightJoin(Class<?> tableClass){
		return join("right", tableClass);
	}
	
	public En rightJoin(String table,String as){
		return join("right",table,as);
	}
	
	public En rightJoin(String table){
		return join("right",table);
	}
	
	public En on(String p1,String p2){
		sqlBuilder.append(" on ")
						.append(p1)
						.append(" = ")
						.append(p2);
		return self();
	}
	
	public En on(String str){
		sqlBuilder.append(" on "+str);
		return self();
	}
	
	public En limit(Integer page,Integer size){
		sqlBuilder.append(" ")
						 .append("limit ? , ?");
		this.addParam((page -1)*size);
		this.addParam(size);
		return self();
	}
	
	public En sql(String sql){
		sqlBuilder.append(" "+sql);
		return self();
	}
	
	public En sql(String sql,Object...objects){
		sqlBuilder.append(" "+sql);
		addParams(objects);
		return self();
	}

	public String createUpdateQuery(Map<String, Object> propMap){
		TableMapping info = getTableMapping();
		for (Entry<String, Object> en : propMap.entrySet()){
				set(en.getKey(),en.getValue());
		}
	   StringBuilder updateSQL = new StringBuilder();
	   updateSQL.append("UPDATE ")
						 .append(info.getName())
						 .append(" SET ");
	    int i = 0;
		for (Entry<String, Object> en : this.setFieldMap.entrySet()) {
			if(i > 0){
				updateSQL.append(" , ");
			}
			updateSQL.append(en.getKey())
							 .append(" = ")
							 .append(en.getValue());
			i++;
		}
		updateSQL.append(sqlBuilder);
	   return updateSQL.toString();
	}
	
	public String createUpdateQuery(){
		Map<String, Object> map = new LinkedHashMap<String, Object>();
		return createUpdateQuery(map);
	}
	
	
	public String createDeleteQuery(){
		TableMapping mapping = getTableMapping();
		StringBuilder deleteSQL = new StringBuilder();
		deleteSQL.append("DELETE")
						.append(" ")
						.append("FROM")
						.append(" ")
						.append(mapping.getName())
						.append(sqlBuilder);
		return deleteSQL.toString();
	}
	
	public String createInsertQuery(Map<String, Object> map){
		TableMapping info = getTableMapping();
		StringBuilder insertSQL = new StringBuilder();
		StringBuilder valueSQL = new StringBuilder();
		insertSQL.append("INSERT INTO ")
					   .append(info.getName())
					   .append(" ")
					   .append("(");
		valueSQL.append(" ")
					  .append("VALUE")
					  .append(" ")
					  .append("(");
		int count = 0;
		for (Entry<String, Object> en : map.entrySet()) {
			if (count > 0) {
				insertSQL.append(",").append(" ");
				valueSQL.append(",").append(" ");
			}
			insertSQL.append(en.getKey());
			valueSQL.append("?");
			this.params.add(en.getValue());
			count++;
		}

		insertSQL.append(")");
		valueSQL.append(")");
		insertSQL.append(valueSQL);
		return insertSQL.toString();
	}
	
	
	protected String createQuerySQL() {
		String[] array = new String[this.fields.size()];
		this.fields.toArray(array);
		return createQuerySQL(array);
	}
	
	protected String createQuerySQL(String[] cols){
		StringBuilder selectSQL = new StringBuilder();
		selectSQL.append("SELECT ");
		int fieldSize = cols.length;
		if(fieldSize > 0){
			for (int i = 0; i < fieldSize; i++) {
				if(i > 0){ 
					selectSQL.append(", ");
				}
				selectSQL.append(cols[i]);
				selectSQL.append(" ");
			}
		}else{
			selectSQL.append("* ");
		}
		selectSQL.append("FROM ");
		selectSQL.append(tables.toString());
		selectSQL.append(" ");
		selectSQL.append(sqlBuilder.toString());
		return selectSQL.toString();
	}
	
	
	public void whereWithProperty(){
		TableMapping info = this.getTableMapping();
		List<PropertyDescriptor> pds = new ArrayList<PropertyDescriptor>();
		pds.add(info.getIdProperty());
		pds.addAll(info.getColumns());
		int len = pds.size();
		int count = 0;
		where();
		for (int i = 0; i < len; i++) {
			PropertyDescriptor pd = pds.get(i);
			Object value = info.getPropertyValue(pd,this);
			if(value != null){
				if(count > 0){
					and();
				}	
				eq(pd.getName(), value);
				count++;
			}
		}
	}
	
	
	
	@SuppressWarnings("unchecked")
	protected  Class<En> getEnClass(){
		try {
			return (Class<En>) (((ParameterizedType) this.getClass().getGenericSuperclass()).getActualTypeArguments()[0]);
		} catch (Exception e) {
			
		}
		return null;
	}
	
	/**
	 * @return
	 */
	@SuppressWarnings("unchecked")
	protected Class<Id> getIdClass(){
		return (Class<Id>) (((ParameterizedType) this.getClass().getGenericSuperclass()).getActualTypeArguments()[1]);
	}
	
	private synchronized TableMapping getTableMapping(Class<?> tableClass){
		String key = tableClass.getName();
		TableMapping tbInfo = TableMap.get(key);
		if(tbInfo == null){
			tbInfo = new TableMapping(tableClass);
			TableMap.put(key, tbInfo);
		}	
		return tbInfo;
	}
	
	protected TableMapping getTableMapping(){
		return getTableMapping(getEnClass());
	}
	
	
	public Map<String,Object> select(){
		 List<Map<String,Object>> list = selectList();
		 if(list != null){
			 if(list.size() > 0)
				 return list.get(0);
		 }
		 return null;
	}
	
	
	public Integer selectTotalCount(){
		return uniqueResult(new String[]{"count(*)"}, Integer.class);
	}
	
	
	
	public Map<String, Object> select(Id id){
		TableMapping mapping = getTableMapping();
		where().eq(mapping.getId(), id);
		return select();
	}
	
	public List<Map<String, Object>> selectList(){
		return db().queryForList(createQuerySQL(),params.toArray());
	}
	
	public List<En> selectModelList(){
		return db().query(createQuerySQL(), new BeanPropertyRowMapper<En>(getEnClass()),params.toArray());
	}
	
	public List<En> selectModelList(Integer page,Integer size){
		 limit(page, size);
		 return selectModelList();
	}
	
	public En selectModel(Id id){
		TableMapping mapping = getTableMapping();
		where().eq(mapping.getId(),id);
		return selectModel();
	}
	
	public En selectModel(){
		List<En> modelList = selectModelList();
		if(modelList.size() > 0){
			return modelList.get(0);
		}
		return null;
	
	}
	
	public List<Map<String, Object>> selectList(Integer page,Integer size){
		limit(page, size);
		return db().queryForList(createQuerySQL(),params.toArray());
	}
	
	public Integer delete(){
		return db().update(createDeleteQuery(),params.toArray());
	}
	
	public Integer delete(Id id){
		TableMapping mapping = getTableMapping();
		return where().eq(mapping.getId(),id).delete();
	}
	
	public Integer update(){
		return db().update(createUpdateQuery(), params.toArray());
	}
	
	public Integer update(Id id){
		TableMapping mapping = getTableMapping();
		this.where().eq(mapping.getId(),id);
		return update();
	}
	
	
	public Integer save(Map<String, Object> map){
		GeneratedKeyHolder keyHolder = new GeneratedKeyHolder();
		PreparedStatementCreator creator = new InsertPreparedStatementCreator(createInsertQuery(map),params.toArray());
		db().update(creator, keyHolder);
		return Converter.convert(Integer.class, keyHolder.getKey().toString());
	}
	
	public Integer save(En en){
		TableMapping mapping = en.getTableMapping();
		List<PropertyDescriptor> cols = mapping.getColumns();
		Map<String, Object> modelMap = new HashMap<String, Object>();
		for (PropertyDescriptor pd : cols) {
			if(mapping.isIdAuto()){
				modelMap.put(pd.getName(),mapping.getPropertyValue(pd, en));
			}
		}
		return save(modelMap);
	}
	
	public Integer save(){
		return save(self());
	}
	
	public <T> T uniqueResult(Class<T> requiredType){
		return db().queryForObject(createQuerySQL(), requiredType,params.toArray());
	}
	
	public <T> T uniqueResult(String[] cols,Class<T> requiredType){
		return db().queryForObject(createQuerySQL(cols), requiredType,params.toArray());
	}
	
	public Integer update(Map<String, Object> model){
		TableMapping mapping = getTableMapping();
		for (Entry<String, Object> en : model.entrySet()) {
			if(!en.getKey().equals(mapping.getId())){
				set(en.getKey(), en.getValue());
			}
		}
		where();
		eq(mapping.getId(), model.get(mapping.getId()));
		return update();
	}
	
	public Page<Map<String, Object>> selectPage(Integer page,Integer size){
		Page<Map<String, Object>> data = new Page<Map<String,Object>>();
		data.setPage(page);
		data.setSize(size);
		data.setTotal(uniqueResult(new String[]{"count(*)"},Integer.class));
		data.setData(selectList(page,size));
		return data;
	}
	
	public boolean hasField(String field,Object value){
		where().eq(field, value);
		return uniqueResult(new String[]{"count(*)"}, Integer.class) > 0;
	}
	
	public boolean exists(){
		cols("count(*)");
		return uniqueResult(Integer.class) > 0;
	}
	
	public En andTerm(boolean term,String str,Object...objects){
		if(term){
			and(str,objects);
		}
		return self();
	}
	
	public En andTerms(List<List<Object>> terms){
		for (List<Object> list : terms) {
			boolean term = (boolean) list.get(0);
			String str = (String) list.get(1);
			List<Object> params = new ArrayList<Object>();
			for (int i = 0; i < list.size(); i++) {
				if(i >= 2){
					params.add(list.get(i));
				}
			}
			andTerm(term, str,params.toArray());
		}
		return self();
	}
	
	
}
